﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//请先添加引用
using Gift.Model;



namespace Gift.DAL
{

    public class OrderAccount
    {
        public OrderAccount()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbOrderType");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public int DeleOrderAccount(string sOrderCode,int iCardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbOrderAccount where ordercode=@OrderCode and cardid = @CardId");
            strSql.Append(" update db_Card set states=1 where CardId=@CardId  and states=5 ");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderCode", SqlDbType.VarChar,50),
                    new SqlParameter("@CardId", SqlDbType.Int, 4)
                                        
                                        };
            parameters[0].Value = sOrderCode;
            parameters[1].Value = iCardId;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public string GenerateOrderCode()
        {
            StringBuilder strSql = new StringBuilder();
            SqlDataReader sdr;
            strSql.Append(" declare @OrderCode int");
            strSql.Append(" set @OrderCode = 0 ");
               strSql.Append("if (select max(OrderCode) from tbOrderAccount) is null");
               strSql.Append(" begin");
               strSql.Append(" set @OrderCode = 910000000 ");
               strSql.Append(" insert into tbOrderAccount(OrderCode) values(910000000) ");
               strSql.Append(" end ");
               strSql.Append(" else ");
               strSql.Append(" begin ");
               strSql.Append(" set @OrderCode = (select  max(OrderCode) + 1 from tbOrderAccount ) ");
               strSql.Append("  insert into tbOrderAccount(OrderCode)  values(@OrderCode) ");
               strSql.Append("  end ");
               strSql.Append("  select @OrderCode ");

               using (sdr = DbHelperSQL.ExecuteReader(strSql.ToString()))
               {
                   if (sdr.Read())
                   {
                       string sResult = "9" + sdr[0].ToString().Substring(1, sdr[0].ToString().Length - 1);
                       sdr.Close();
                       return sResult;
                   }
                   else
                   {
                       sdr.Close();
                       return null;
                   }
               }
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public string GenerateOrderCodeForBefor()
        {
            StringBuilder strSql = new StringBuilder();
            SqlDataReader sdr;
            strSql.Append(" declare @OrderCode int");
            strSql.Append(" set @OrderCode = 0 ");
            strSql.Append("if (select max(OrderCode) from tbOrderAccount) is null");
            strSql.Append(" begin");
            strSql.Append(" set @OrderCode = 810000000 ");
            strSql.Append(" insert into tbOrderAccount(OrderCode) values(810000000) ");
            strSql.Append(" end ");
            strSql.Append(" else ");
            strSql.Append(" begin ");
            strSql.Append(" set @OrderCode = (select  max(OrderCode) + 1 from tbOrderAccount ) ");
            strSql.Append("  insert into tbOrderAccount(OrderCode)  values(@OrderCode) ");
            strSql.Append("  end ");
            strSql.Append("  select @OrderCode ");

            sdr = DbHelperSQL.ExecuteReader(strSql.ToString());
            if (sdr.Read())
            {
                string sResult = string.Empty;
                sResult =  "8" +  sdr[0].ToString().Substring(1,sdr[0].ToString().Length - 1);
                sdr.Close();
                return sResult;
            }
            else
            {
                sdr.Close();
                return null;
            }
        }

        /// <summary>
        /// 生成手机订单号
        /// </summary>
        public string GenerateMobileOrderCode()
        {
            StringBuilder strSql = new StringBuilder();
            SqlDataReader sdr;
            strSql.Append(" declare @OrderCode int");
            strSql.Append(" set @OrderCode = 0 ");
            strSql.Append("if (select max(OrderCode) from tbOrderAccount where OrderCode like '7%') is null");
            strSql.Append(" begin");
            strSql.Append(" set @OrderCode = 710000000 ");
            strSql.Append(" insert into tbOrderAccount(OrderCode) values(710000000) ");
            strSql.Append(" end ");
            strSql.Append(" else ");
            strSql.Append(" begin ");
            strSql.Append(" set @OrderCode = (select  max(OrderCode) + 1 from tbOrderAccount where OrderCode like '7%' ) ");
            strSql.Append("  insert into tbOrderAccount(OrderCode)  values(@OrderCode) ");
            strSql.Append("  end ");
            strSql.Append("  select @OrderCode ");

            sdr = DbHelperSQL.ExecuteReader(strSql.ToString());
            if (sdr.Read())
            {
                string sResult = "7" + sdr[0].ToString().Substring(1, sdr[0].ToString().Length - 1);
                sdr.Close();
                return sResult;
            }
            else
            {
                sdr.Close();
                return null;
            }
        }

  


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(tbOrderAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbOrderType(");
            strSql.Append("OrderCode,CardId,CardAdded)");
            strSql.Append(" values (");
            strSql.Append("@OrderCode,@CardId,@CardAdded)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderCode", SqlDbType.NVarChar,50),
					new SqlParameter("@CardId", SqlDbType.Int,4),
					new SqlParameter("@CardAdded", SqlDbType.Bit,1)};
            parameters[0].Value = model.OrderCode;
            parameters[2].Value = model.CardId;
            parameters[3].Value = model.CardAdded;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 添加卡内钱到订单账号
        /// </summary>
        public int AddCardToCardAccount(string OrderCode, int CardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("if exists(select 1 from DB_Card where cardid = @CardId and states = 1 and convert(datetime,convert(char(10),getdate(),121)) <= endtime) ");
             strSql.Append(" begin ");
          //   strSql.Append("    delete from tbOrderAccount where CardId is null and OrderCode = @OrderCode ");
              strSql.Append("   update DB_Card set states = 5 where cardid = @CardId ");
              strSql.Append(" if not exists(select 1 from tbOrderAccount where OrderCode=@OrderCode and CardId=@CardId)");
                strSql.Append( " begin ");
              strSql.Append("    insert into tbOrderAccount(OrderCode,CardId) ");
              strSql.Append("   values(@OrderCode,@CardId) ");
            strSql.Append( " end ");
              strSql.Append("  select c.cardid, c.cardcode, isnull(c.cardprice,0) as cardprice,c.cardtypeId from tbOrderAccount O join DB_Card c ON o.CardId = c.cardid where isnull(o.CardAdded,0) = 0 and o.OrderCode = @OrderCode ");
              strSql.Append("   end  ");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderCode", SqlDbType.NVarChar,50),
					new SqlParameter("@CardId", SqlDbType.Int,4)};
            parameters[0].Value = OrderCode;
            parameters[1].Value = CardId;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }


        /// 根据卡编号获取商品
        /// </summary>
        public DataTable GetGoodsByCardId(int CardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct s.goodsId,s.goodsName from tbGoods s join tbGoodsCardType gt on s.goodsId = gt.goodsId ");
            strSql.Append( " and isSale = 1 and visible = 1 join tbCardType ct on gt.cardTypeId = ct.id ");
            strSql.Append(" join db_card c on c.CardTypeId = ct.Id and CardId = @CardId and c.useWay=1 and getdate() between starttime and endtime ");
            SqlParameter[] parameters = {
					new SqlParameter("@CardId", SqlDbType.Int,4)};
            parameters[0].Value = CardId;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }

        }

     


        /// <summary>
        ///返回账户内余额
        /// </summary>
        public List<DB_Card> GetCardAccountList(string OrderCode)
        {
            StringBuilder strSql = new StringBuilder();
            List<DB_Card> lstCard = new List<DB_Card>();
            DB_Card oCard;
            strSql.Append("  select c.cardid, c.cardcode, isnull(c.cardprice,0) as cardprice,c.cardtypeId,c.cardpas from tbOrderAccount O join DB_Card c ON o.CardId = c.cardid where isnull(o.CardAdded,0) = 0 and o.OrderCode = @OrderCode ");

            SqlParameter[] parameters = {
					new SqlParameter("@OrderCode", SqlDbType.NVarChar,50)};
            parameters[0].Value = OrderCode;

            SqlDataReader dr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            while (dr.Read())
            {
                oCard = new DB_Card();
                if (dr[0].ToString() != "")
                {
                    oCard.cardid = int.Parse(dr[0].ToString());
                }
                oCard.cardcode = dr[1].ToString();
                oCard.cardprice = int.Parse(dr[2].ToString());
                if (dr[3].ToString() != "")
                {
                    oCard.cardtypeId = int.Parse(dr[3].ToString());
                }
                if (dr[4].ToString() != "")
                {
                    oCard.cardpas = dr[4].ToString();
                }
                lstCard.Add(oCard);
            }
            dr.Close();
            return lstCard;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(tbOrderAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbOrderType set ");
            strSql.Append("OrderCode=@OrderCode,");
            strSql.Append("CardId=@CardId,");
            strSql.Append("CardAdded=@CardAdded");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@OrderCode", SqlDbType.NVarChar,50),
					new SqlParameter("@CardId", SqlDbType.Int,4),
					new SqlParameter("@CardAdded", SqlDbType.Bit,1)};
            parameters[0].Value = model.Id;
            parameters[1].Value = model.OrderCode;
            parameters[3].Value = model.CardId;
            parameters[4].Value = model.CardAdded;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete tbOrderType ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public tbOrderAccount GetModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,OrderCode,CardId,CardAdded from tbOrderType ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            tbOrderAccount model = new tbOrderAccount();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.OrderCode = ds.Tables[0].Rows[0]["OrderCode"].ToString();
                if (ds.Tables[0].Rows[0]["CardId"].ToString() != "")
                {
                    model.CardId = int.Parse(ds.Tables[0].Rows[0]["CardId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CardAdded"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["CardAdded"].ToString() == "1") || (ds.Tables[0].Rows[0]["CardAdded"].ToString().ToLower() == "true"))
                    {
                        model.CardAdded = true;
                    }
                    else
                    {
                        model.CardAdded = false;
                    }
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,OrderCode,CardId,CardAdded ");
            strSql.Append(" FROM tbOrderAccount ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetNoComplateAccountList()
        {
            StringBuilder strSql = new StringBuilder();
          //  strSql.Append(" delete from tbOrderAccount where isnull(CardId,0)=0 and isnull(CardAdded,0)=0 and datediff(day,CardAdded,getdate())>=1;");
            strSql.Append("select distinct OrderCode ");
            strSql.Append(" FROM tbOrderAccount where isnull(CardAdded,0) = 0 order by OrderCode asc ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public void UpdateComplateAccount(string OrderCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update tbOrderAccount set CardAdded = 1 where OrderCode = @OrderCode; ");
            strSql.Append(" update DB_Card set states = 2,UsedTime=getdate() where  cardid in (select cardid from tbOrderAccount where OrderCode = @OrderCode); ");
            SqlParameter[] parameters = {
                    new SqlParameter("@OrderCode", SqlDbType.VarChar,50)};
            parameters[0].Value = OrderCode;
             DbHelperSQL.Query(strSql.ToString(),parameters);

        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@ProfessionArearTypeId", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbProfessionArearTypeId";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  成员方法
    }
}

